﻿Imports System.Data
Imports System.Data.OleDb
Imports System.Web.Configuration
Partial Class search
    Inherits System.Web.UI.Page

    Protected Sub Button_search_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_search.Click
        'hide "no results" message
        Me.div_noResults.Visible = False
        'change button text
        Me.Button_search.Text = "Search Again"
        'clear gridview of previous search results
        Me.GridView_results.DataSource = Nothing
        Me.GridView_results.DataBind()
        'declare and instantiate connection object - connection string is stored in web.config
        Dim myConnection = New OleDbConnection()
        myConnection.ConnectionString = WebConfigurationManager.ConnectionStrings("yobboGuitar").ConnectionString
        'build SQL query up as a string
        Dim whereFlag As Boolean = False 'flag that is set when first WHERE clause is added to query text
        Dim queryText As String = "SELECT * FROM songListing "
        If DropDownList_bands.SelectedValue <> "-1" Then
            queryText = queryText & "WHERE bandID=" & DropDownList_bands.SelectedValue & " "
            whereFlag = True
        End If
        If DropDownList_genres.SelectedValue <> "-1" Then
            If whereFlag = False Then
                queryText = queryText & "WHERE " 'add WHERE keyword if not already added
                whereFlag = True
            Else
                queryText = queryText & "AND " 'add AND keyword, since there is already a WHERE clause present
            End If
            queryText = queryText & "genreID=" & DropDownList_genres.SelectedValue & " "
        End If

        If Trim(Me.TextBox_words.Text) <> "" Then 'add appropriate WHERE clauses for phrase or words
            If whereFlag = False Then
                queryText = queryText & "WHERE " 'add WHERE keyword if not already added
                whereFlag = True
            Else
                queryText = queryText & "AND " 'add AND keyword, since there is already a WHERE clause present
            End If
            If Me.RadioButtonList_phraseOrWords.SelectedValue = "phrase" Then
                'use LIKE operator to search for actual phrase entered
                queryText = queryText & "title Like ""%" & Me.TextBox_words.Text & "%"" "
            Else
                'use LIKE operator to search for titles containing all individual words (in any order)
                Dim words() As String = Split(Me.TextBox_words.Text, " ") 'split search text into array of individual words
                For i As Integer = 0 To UBound(words) 'loop to build up query
                    If i < UBound(words) Then
                        queryText = queryText & " title Like ""%" & words(i) & "%"" AND " 'add LIKE expression followed by AND operator
                    Else
                        queryText = queryText & " title Like ""%" & words(i) & "%"" " 'skip AND operator, as last time through
                    End If
                Next
            End If
        End If

        'add ORDER BY clause
        queryText = queryText & "ORDER BY " & Me.DropDownList_sortBy.SelectedValue

        'declare and instantiate command object
        Dim myCommand = New OleDbCommand(queryText)
        Try
            'open connection
            myConnection.Open()
            'set connection property of command object
            myCommand.Connection = myConnection
            'declare reader
            Dim myReader As OleDbDataReader
            'execute reader
            myReader = myCommand.ExecuteReader
            If myReader.HasRows Then
                'set data source property of grid view and invoke dataBind method to fill control
                Me.GridView_results.DataSource = myReader
                Me.GridView_results.DataBind()
            Else
                'display "no results" message
                Me.div_noResults.Visible = True
            End If

            'close connection
            myConnection.Close()
        Catch ex As Exception
            'redirect to error message page
            Response.Redirect("~/error.htm")
        Finally
            myConnection.Close()
        End Try
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            'populate drop down lists for bands and genres from database

            'declare and instantiate connection object - connection string is stored in web.config
            Dim myConnection = New OleDbConnection()
            myConnection.ConnectionString = WebConfigurationManager.ConnectionStrings("yobboGuitar").ConnectionString

            'create command objects
            Dim myCommand_queryBands = New OleDbCommand("SELECT * from bands ORDER BY bandName;")
            Dim myCommand_queryGenres = New OleDbCommand("SELECT * from genres ORDER BY genreName;")
            Try
                'open connection
                myConnection.Open()
                'set connection property of command objects
                myCommand_queryBands.Connection = myConnection
                myCommand_queryGenres.Connection = myConnection
                'declare readers
                Dim myReader_bands As OleDbDataReader
                Dim myReader_genres As OleDbDataReader
                'execute readers
                myReader_bands = myCommand_queryBands.ExecuteReader
                myReader_genres = myCommand_queryGenres.ExecuteReader
                'prevent existing "-any-" menu items from being cleared on data binding
                Me.DropDownList_bands.AppendDataBoundItems = True
                Me.DropDownList_genres.AppendDataBoundItems = True
                'set data source, data value and data text field properties and invoke dataBind method to fill drop down list boxes
                Me.DropDownList_bands.DataSource = myReader_bands
                Me.DropDownList_bands.DataValueField = "ID"
                Me.DropDownList_bands.DataTextField = "bandName"
                Me.DropDownList_bands.DataBind()
                Me.DropDownList_genres.DataSource = myReader_genres
                Me.DropDownList_genres.DataValueField = "ID"
                Me.DropDownList_genres.DataTextField = "genreName"
                Me.DropDownList_genres.DataBind()
                'close connection
                myConnection.Close()
            Catch ex As Exception
                'redirect to error message page
                Response.Redirect("~/error.htm")
            Finally
                myConnection.Close()
            End Try
            
        End If
    End Sub

    Protected Sub LinkButton_tagging_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LinkButton_tagging.Click
        Response.Redirect("~/tagging.aspx")
    End Sub
End Class
